﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Softpro.AccesoDatosDB2;

namespace Consulta.Manager
{
    public class MContabilidad
    {
        public DataTable leerMovimientoContable(string pCuenta, string pCCosto,string pNit,string pAnno, string pMesIni, string pMesFin, 
                                                bool pBAC,bool pBAN, bool pAGC, bool pTER,bool pFTV, bool pAISC, bool pINCA, string pClausulaOrderBy)
        {
            try
            {
                string tSql="";
                string tWhereSql, tLib;
                DataTable tabla = new DataTable();
                accesoDatos p = new accesoDatos();
                if ((pCuenta != "0" || pNit !="0" || pCCosto !="0" ) && (pBAC||pBAN||pAGC||pTER||pFTV||pAISC||pINCA))
                {
                    //Crear cadena de campos a leer
                    string tCamposSql = "T1.JMMAA As Periodo, T1.CTCT5 As CCosto, T1.IFecha As Fecha, T1.JTICOM AS Tipo, T1.JNUCOM AS Numero, " +
				                "T1.ICONS AS Secuencia, T1.IDESC AS Descripcion, T1.IREF1 AS Ref1 , T1.IREF2 AS Ref2, " +
				                "T1.ICDTER As Nit, T1.IVALOR As Valor , T2.MDESC ";
                    //Definir las condiciones de busqueda
                    string tAnno = pAnno.Substring(2, 2);
                    if (pCuenta.Length >0)
                    {
                        //Buscar cuenta contable
                        string[] tCuenta = pCuenta.Split('.');
		                tWhereSql =	" WHERE T1.JMMAA >= " + pMesIni + tAnno + " AND T1.JMMAA <= " + pMesFin + tAnno +
					                " AND T1.CTCT1 = " + tCuenta[0] + " AND T1.CTCT2 = " + tCuenta[1] + 
					                " AND T1.CTCT3 = " + tCuenta[2] + " AND T1.CTCT4 = " + tCuenta[3] + 
					                " AND T1.CTCT1 = T2.MCTA1 AND T1.CTCT2 = T2.MCTA2 AND T1.CTCT3 = T2.MCTA3 AND T1.CTCT4 = T2.MCTA4";
                    }
	                else
                    {
                        //Buscar por nit y/o centro de costo
                        tWhereSql = " WHERE T1.JMMAA >= " + pMesIni + tAnno + " AND T1.JMMAA <= " + pMesFin + tAnno + 
                                    " AND T1.CTCT1 = T2.MCTA1 AND T1.CTCT2 = T2.MCTA2 AND T1.CTCT3 = T2.MCTA3 AND T1.CTCT4 = T2.MCTA4";
	                }
	                if (pCCosto.Length > 0 && pCCosto != "0")
                    {
		                tWhereSql += " AND T1.CTCT5 = " + pCCosto;
                    }
                    if (pNit.Length > 0 && pNit != "0")
                    {
		                tWhereSql += " AND T1.ICDTER = " + pNit;
                    }
                    //Definir campo compañia y librerias de datos FROM
                    if (pBAC)
                    {
		                tLib = "CND" + tAnno + "003";
                        tSql = "SELECT 'BAC' as Cia, " + tCamposSql +
                                 " FROM " + tLib + ".CNMPAEDP As T1, " + tLib + ".CNCTAEDL As T2 " + tWhereSql;
                    }
	                if (pBAN)
                    {
		                if (tSql.Length >0)
                        {
			                tSql = tSql + " UNION ";
		                }
		                tLib = "CND" + tAnno + "004";
		                tSql = tSql + " SELECT 'BAN' as Cia, " + tCamposSql +
				                "FROM " + tLib + ".CNMPAEDP As T1, " + tLib + ".CNCTAEDL As T2 " + tWhereSql;
	                }
	                if (pAGC)
                    {
		                if (tSql.Length >0)
                        {
			                tSql = tSql + " UNION ";
		                }
		                tLib = "CND" + tAnno + "001";
		                tSql = tSql + " SELECT 'AGC' as Cia, " + tCamposSql +
				                "FROM " + tLib + ".CNMPAEDP As T1, " + tLib + ".CNCTAEDL As T2 " + tWhereSql;
	                }
	                if (pTER)
                    {
		                if (tSql.Length >0)
                        {
			                tSql = tSql + " UNION ";
		                }
		                tLib = "CND" + tAnno + "007";
		                tSql = tSql + " SELECT 'TER' as Cia, " + tCamposSql +
				                "FROM " + tLib + ".CNMPAEDP As T1, " + tLib + ".CNCTAEDL As T2 " + tWhereSql;
	                }
                    if (pFTV)
                    {
                        if (tSql.Length >0)
                        {
                            tSql = tSql + " UNION ";
                        }
                        tLib = "CND" + tAnno + "006";
                        tSql = tSql + " SELECT 'FTV' as Cia, " + tCamposSql +
                                "FROM " + tLib + ".CNMPAEDP As T1, " + tLib + ".CNCTAEDL As T2 " + tWhereSql;
                    }
                    if (pAISC)
                    {
                        if (tSql.Length >0)
                        {
                            tSql = tSql + " UNION ";
                        }
                        tLib = "CND" + tAnno + "005";
                        tSql = tSql + " SELECT 'CAY' as Cia, " + tCamposSql +
                                "FROM " + tLib + ".CNMPAEDP As T1, " + tLib + ".CNCTAEDL As T2 " + tWhereSql;
                    }
                    if (pINCA)
                    {
                        if (tSql.Length >0)
                        {
                            tSql = tSql + " UNION ";
                        }
                        tLib = "CND" + tAnno + "008";
                        tSql = tSql + " SELECT 'INC' as Cia, " + tCamposSql +
                            "	FROM " + tLib + ".CNMPAEDP As T1, " + tLib + ".CNCTAEDL As T2 " + tWhereSql;
                    }
	                if (tSql.Length >0)
                    {
		                tSql = tSql + " ORDER BY Cia, Periodo, Fecha";
	                }
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        public string leerNombreCuentaContable(string pCuenta, string pCiaCod, string pAnno)
        {
            try
            {
                string tValor="";
                if (pCuenta != "0" )
                {
                    string tSql = "";
                    string tLibDatos = "003";
                    DataTable tabla = new DataTable();
                    accesoDatos p = new accesoDatos();
                    string tAnno = pAnno.Substring(2, 2);
                    if (pCuenta.Length > 0)
                    {
                        //Buscar cuenta contable
                        string[] tCuenta = pCuenta.Split('.');
                        //Definir campo compañia y librerias de datos FROM
                        switch (pCiaCod)
                        {
                            case "BAC":
                                tLibDatos = "003";
                                break;
                            case "TER":
                                tLibDatos = "007";
                                break;
                            case "AGC":
                                tLibDatos = "001";
                                break;
                            case "FTV":
                                tLibDatos = "006";
                                break;
                            case "INC":
                                tLibDatos = "008";
                                break;
                            case "CAY":
                                tLibDatos = "005";
                                break;
                            case "BAN":
                                tLibDatos = "004";
                                break;
                        }
                        tSql = "SELECT MDESC FROM CND" + tAnno + tLibDatos + ".CNCTAEDL " +
                                " WHERE MCTA1 = " + tCuenta[0] +
                                " AND MCTA2 = " + tCuenta[1] +
                                " AND MCTA3 = " + tCuenta[2] +
                                " AND MCTA4 = " + tCuenta[3];
                        tValor = p.leerValorSQL(tSql);
                    }
                }
                return tValor;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }

        }
    }
}
